﻿CREATE PROCEDURE [dbo].[proc_Customer_GetItem_20141107]
	(
		@CustomerId uniqueidentifier,
		@UserId varchar(30),
		@rs int
	)
AS
Begin
	Declare @CompanyId int,@ProtectId int,@ProtectName nvarchar(30),@ProtectDate datetime
	Declare @CusName nvarchar(100),@ProtectUser varchar(30),@ServiceUser varchar(30),@BDServiceUser varchar(30),@UserName nvarchar(30),@UpTime datetime

	Select @CompanyId=CompanyId,
	@CusName=CustomerName,
	@UserName=IsNull((Select Top 1 EName From Employee Where UserName=@UserId),''),
	@ProtectId=IsNull(ProtectId,0),
	@ProtectUser=IsNull(ProtectName,''),
	@ServiceUser=IsNull(serviceperson,''),	--维护客服
	@BDServiceUser=IsNull(BDserviceperson,''),	--百度客服
	@ProtectName=IsNull((Select Top 1 EName From Employee Where UserName=ProtectName),''),
	@ProtectDate=IsNull(ProtectDate,'1900-01-01'),
	@UpTime=IsNull(UpTime,GetDate())
	From Customer
	Where CustomerId=@CustomerId;

	--客户信息
	Select Id,CompanyId,CustomerId,CustomerName,Project,
		ISNull(Webaddress,'') As Webaddress,
		Tel,Fax,Address,Zip,CreateName,
		IsNull(IndustryOneId,0) As IndustryOneId,
		IsNull(IndustryTwoId,0) As IndustryTwoId,
		IsNull(SourceId,0) As SourceId,
		IsNull(StateId,0) As StateId,
		IsNull(TypeId,0) As TypeId,
		ISNULL(CreateDate,'1900-01-01') As CreateDate,
		IsNull(ProtectId,1) As ProtectId,
		IsNull(Pid,1) As Pid,IsNull(Cid,1) As Cid,IsNull(Rid,1) As Rid,
		IntentionsId,PublicityDemandId,NetworkConsciousnessId,MnemonicWorks,ProtectName,
		ISNULL(ProtectDate,'1900-01-01') As ProtectDate,
		(Select Title From Customer_Industry Where Id=Customer.IndustryOneId) As IndustryOne,
		IsNull((Select Top 1 Title From Customer_Industry Where Id=Customer.IndustryTwoId),'...') As IndustryTwo,
		IsNull((Select Top 1 Title From Customer_Source Where Id=Customer.SourceId),'...') As Source,
		IsNull((Select Top 1 Title From Customer_State Where Id=Customer.StateId),'...') As State,
		IsNull((Select Top 1 Title From Customer_Type Where Id=Customer.TypeId),'...') As Type,
		IsNull((Select Top 1 Title From Customer_Protect Where Id=Customer.ProtectId),'...') As Protect,
		IsNull((Select Top 1 CompanyName From Company Where Id=Customer.CompanyId),'...') As Company,
		IsNull((Select Top 1 Title From Province Where Id=Customer.pid),'...') As Province,
		IsNull((Select Top 1 Title From City Where [Level]=2 And Id=Customer.Cid),'...') As City,
		IsNull((Select Top 1 Title From City Where [Level]=3 And Id=Customer.Rid),'...') As Region,
		IsNull((Select Top 1 Title From Customer_Intentions Where Id=Customer.IntentionsId),'...') As Intentions,
		IsNull((Select Top 1 Title From Customer_PublicityDemand Where Id=Customer.PublicityDemandId),'...') As PublicityDemand,
		IsNull((Select Top 1 Title From Customer_NetworkConsciousness Where Id=Customer.NetworkConsciousnessId),'...') As NetworkConsciousness,
		IsNull((Select Top 1 EName From Employee Where UserName=Customer.CreateName And UserName<>''),'...') As CreateNameEName,
		IsNull((Select Top 1 EName From Employee Where UserName=Customer.ProtectName And UserName<>''),'...') As ProtectNameEName,
		SalesPerson,ServicePerson,
		IsNull((Select EName From Employee Where UserName=Customer.SalesPerson And UserName<>''),'...') As SalesPersonEName,
		IsNull(ServicePersonEName,'...') ServicePersonEName,
		OrdersCount,delFlag,Audit,ExtractDate,
		ISNULL((Select Top 1 AuditDate From Customer_Auditlog Where CustomerId=Customer.CustomerId Order By AuditDate Desc),'1900-01-01') As AuditDate,
		IsNull((Select Top 1 EName From Employee Where UserName In(Select Top 1 AuditUser From Customer_Auditlog Where CustomerId=Customer.CustomerId Order By AuditDate Desc)),'...') As AuditUser,
		IsNull(BDServicePerson,'') BDServicePerson,
		IsNull(BDServicePersonEName,'...') as BDServicePersonEName,
		RegTime,
		RegCapital,
		Employees,
		(Select Title From Customer_Employees where Id=Customer.Employees) EmployeesName,
		Introduction

	From Customer
	Where CustomerId=@CustomerId;

	--联系方式
	Select Id,CustomerId,PersonId,ZName,Sex,Age,Weixin,
	[Position],OfficeTel,HomeTel,Mobile,Email,
	CreateDate,IsMain,PersonTypeId,
	(Select Title From Contact_Type Where Id=ContactPerson.PersonTypeId) As PersonType
	From ContactPerson
	Where CustomerId=@CustomerId
	Order by IsMain DESC,CreateDate Desc;

	--批注记录
	WITH List As(Select ROW_NUMBER() OVER (Order By id Desc) AS Row,
		id,IntentionsId,-- PublicityDemandId,NetworkConsciousnessId,
		GenjinId,IsNull(Record,'') As Record,UpName,UpTime
		From Customer_Track
	    Where CustomerId=@CustomerId
	)
	Select *,
	Case When UpName='' Then '...' Else	IsNull((Select Top 1 EName From Employee Where UserName=List.UpName),'...') End As EName,
	IsNull((Select Title From Customer_Intentions Where Id=List.IntentionsId),'...') As Intentions,
--	IsNull((Select Phases From Customer_PublicityDemand Where Id=List.PublicityDemandId),'...') As PublicityDemand,
--	IsNull((Select Title From Customer_NetworkConsciousness Where Id=List.NetworkConsciousnessId),'...') As NetworkConsciousness,
	IsNull((Select Title From Customer_Genjin Where Id=List.GenjinId),'...') As Genjin,
	(Select Count(0) From List) As RecordCount
	From List
	Where Row Between 1 And 5
	Order By Row;

	--完善记录
	If @rs<=3
		Select a.Id,a.SubjoinId,a.Description,
		IsNull((Select Top 1 EName From Employee Where UserName=a.UpName),'') As UpName,
		Convert(varchar(10),a.UpTime,23) As UpTime
		From Customer_Subjoin_List a,(Select SubjoinId,Max(Id) As Id
			From Customer_Subjoin_List
			Where CustomerId=Convert(varchar(60),@CustomerId)
			Group By SubjoinId
			) b
		Where a.Id=b.Id
		Order by a.Id Desc;

	--出访记录
	If @rs>3
		Select Distinct Aim,[Content],Accompany,Convert(varchar(10),CheckOutTime,23) As CheckOutTime
			,IsNull((Select Top 1 Title From Product Where Id=MainProjectId),'') As ProCategory
			,IsNull((Select Top 1 EName From Employee Where UserName=CheckInOut.UserName),'') As UserName
		From CheckInOut
		Where CustomerId=@CustomerId
		Order By CheckOutTime Desc;

	--成单记录
	If @rs>4
		WITH List2 As(Select ROW_NUMBER() OVER (Order By id Desc) AS Row,OId,
			OrderId,Salesman,YingShouPrice,ShiShouPrice,CreateDate
			From [Order]
			Where CusId=@CustomerId And StateId>1
		)
		Select *,
		(YingShouPrice-ShiShouPrice) As WaitPrice,
		(Select Count(0) From List2) As RecordCount,
		(Select Sum(YingShouPrice) From List2) As OrderAmount
		From List2
		Where Row Between 1 And 5
		Order By Row;

	--记录哈尔滨、大连、山西的客户，非本人查看跟踪记录的情况
	If @CompanyId In(1, 22,50) And @ProtectUser<>@UserId And @ServiceUser<>@UserId And @BDServiceUser<>@UserId
		Begin
			Declare @tmpUpTime datetime
			Select Top 1 @tmpUpTime = IsNull(UpTime,'1900-01-01') From Customer_View_Log Where CusName=@CusName And UserName=@UserName Order By UpTime Desc
			If DateDiff(n,@tmpUpTime,Getdate())>30	--该员工最后1次浏览此客户已超过30分钟
			Begin
				If @ProtectId=1 Or @ProtectId=3	--(公库、已签单)
					Begin
						If DateDiff(n,@UpTime,Getdate())>60	--距最后的批注日期已超过1小时
							Insert Into Customer_View_Log(CusId,CusName,UserId,UserName,ProtectId,ProtectUser,ProtectName,ProtectDate,CompanyId)
							Values(@CustomerId,@CusName,@UserId,@UserName,@ProtectId,@ProtectUser,@ProtectName,@UpTime,@CompanyId)
					End
				Else	--保护、跟踪(2,4)
					Insert Into Customer_View_Log(CusId,CusName,UserId,UserName,ProtectId,ProtectUser,ProtectName,ProtectDate,CompanyId)
					Values(@CustomerId,@CusName,@UserId,@UserName,@ProtectId,@ProtectUser,@ProtectName,@ProtectDate,@CompanyId)
			End
		End
End
